package com.tongji.run;

import com.ibeetl.admin.core.util.DateUtil;
import com.ibeetl.admin.core.util.PlatformException;
import com.tongji.entity.Staff;
import com.tongji.utils.DateUtils;
import com.tongji.utils.JxlsUtils;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * 职工任务数统计
 */
public class 每月员工统计 {


    public static Date dateRangeMonth = DateUtil.getDate("2024-05-01");
    public static String dateStringRangeStart = "202405010000000";
    public static String dateStringRangeEnd =   "20240520359999";
    public static String root = "E:\\文档\\2024\\每月统计\\" + DateUtils.getMonth(dateRangeMonth) + "月\\程序查询";

    public static SQLManager taibangSqlManager() {
        ConnectionSource source = ConnectionSourceHelper.getSimple("com.mysql.cj.jdbc.Driver", "jdbc:mysql://10.1.17.253:3306/plasma?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=true", "plasma", "szrouting2004plasma");
        DBStyle mysql = new MySqlStyle();
        // sql语句放在classpagth的/sql 目录下
        SQLLoader loader = new ClasspathLoader("/sql");
        // 数据库命名跟java命名一样，所以采用DefaultNameConversion，还有一个是UnderlinedNameConversion，下划线风格的，
        UnderlinedNameConversion nc = new  UnderlinedNameConversion();
        // 最后，创建一个SQLManager,DebugInterceptor 不是必须的，但可以通过它查看sql执行情况
        SQLManager sqlManager = new SQLManager(mysql,loader,source,nc,new Interceptor[]{new DebugInterceptor()});

        return sqlManager;
    }

    public static void main(String[] args) {
        ConnectionSource source = ConnectionSourceHelper.getSimple("com.mysql.cj.jdbc.Driver", "jdbc:mysql://127.0.0.1:3306/tongji?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=true", "root", "");
        DBStyle mysql = new MySqlStyle();
        // sql语句放在classpagth的/sql 目录下
        SQLLoader loader = new ClasspathLoader("/sql");
        // 数据库命名跟java命名一样，所以采用DefaultNameConversion，还有一个是UnderlinedNameConversion，下划线风格的，
        UnderlinedNameConversion nc = new  UnderlinedNameConversion();
        // 最后，创建一个SQLManager,DebugInterceptor 不是必须的，但可以通过它查看sql执行情况
        SQLManager sqlManager = new SQLManager(mysql,loader,source,nc,new Interceptor[]{new DebugInterceptor()});

        List<Staff> staffList = sqlManager.execute(new SQLReady("select * from member", new Object[] {}), Staff.class);

        Map model = new HashMap();
        model.put("staffList", staffList);
        for (Iterator<Staff> iterator = staffList.iterator(); iterator.hasNext(); ) {
            Staff staff =  iterator.next();
            getStaff(staff);
        }


        System.out.println(model);


        String excelTemplate = "excelTemplates/员工/员工.xlsx";
        try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
            if(is==null) {
                throw new PlatformException("模板资源不存在："+excelTemplate);
            }
            File f = new File(root + "\\员工" +  DateUtils.getMonth(dateRangeMonth) + "月.xlsx");
            f.getParentFile().mkdirs();
            FileOutputStream os = new FileOutputStream(f);
            JxlsUtils.exportExcel(is,os,model);
        } catch (IOException e) {
            throw new PlatformException(e.getMessage());
        }

    }


    public static Map getStaff(Staff staff) {
        SQLManager sqlManager = taibangSqlManager();

        //查询时间范围内员工名下浆员的采浆袋数
        //List<HashMap> staffDaishu = sqlManager.execute(new SQLReady("select count(*) as 袋数, donor.donorId as 浆员编号 , donor.donorName as 浆员姓名, donor.remark as 备注 from t_bims_expense_payment as pay left join t_bims_code_area area on pay.codeArea = area.ccode left join t_bims_donor donor on pay.donorId = donor.donorId where pay.codePremium = 'TRANSPORTEXPENSE' and pay.collectDate between ? and ? and donor.issuingDate between ? and ? and donor.remark like ?", new Object[] {dateStringRangeStart,dateStringRangeEnd,dateStringRangeStart,dateStringRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        //查询时间范围内员工名下浆员的采浆袋数
        List<HashMap> staffDaishu = sqlManager.execute(new SQLReady("select count(*) as 袋数, donor.donorId as 浆员编号 , donor.donorName as 浆员姓名, donor.remark as 备注 from t_bims_expense_payment as pay left join t_bims_code_area area on pay.codeArea = area.ccode left join t_bims_donor donor on pay.donorId = donor.donorId where pay.codePremium = 'TRANSPORTEXPENSE' and pay.collectDate between ? and ? and donor.remark like ?", new Object[] {dateStringRangeStart,dateStringRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long daishu = (Long)staffDaishu.get(0).get("袋数");
        staff.setDaishu(daishu.intValue());

        //查询时间范围内员工名下新浆员人数
        List<HashMap> xinCountList = sqlManager.execute(new SQLReady("select count(*) as 人数 from t_bims_donor as donor where donor.firstCollectDate between ? and ? and donor.remark like ?", new Object[] {dateStringRangeStart,dateStringRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long xinCount = (Long)xinCountList.get(0).get("人数");
        staff.setXinCount(xinCount.intValue());

        //查询时间范围内员工名下超半年浆员人数
        List<HashMap> chaoCountList = sqlManager.execute(new SQLReady("select count(*) as 人数 from (\n" +
                "select *,(TO_DAYS(DATE_FORMAT(left(curColDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(collectionDate,8),'%Y%m%d'))) as days from (\n" +
                "\tselect \n" +
                "     donorName,donorId as aa,curColDate,\n" +
                "\t\t (select c.beginDate from t_bims_plasma_collection as c where c.donorId = aa and c.beginDate < curColDate and c.weight > 0 and c.collectionNumberFlag = 'Y' order by c.beginDate desc limit 0,1 ) as collectionDate\n" +
                "\tfrom (\n" +
                "\t   select donor.donorName as donorName,donor.donorId as donorId, currentCollect.beginDate as curColDate from t_bims_plasma_collection as currentCollect left join t_bims_donor as donor on currentCollect.donorId = donor.donorId where currentCollect.collectionNumberFlag = 'Y' and " +
                " currentCollect.beginDate between ? and ? and donor.remark like ? \n" +
                "\t) as a\n" +
                ") as b) as c where c.days > (180)", new Object[] {dateStringRangeStart,dateStringRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long chaoCount = (Long)chaoCountList.get(0).get("人数");
        staff.setChaoCount(chaoCount.intValue());

        //查询时间范围内员工名下检疫期浆员采浆人数
        List<HashMap> jianXianList = sqlManager.execute(new SQLReady("select count(*) as 人数 from (\n" +
                "select *,(TO_DAYS(DATE_FORMAT(left(curColDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(collectionDate,8),'%Y%m%d'))) as days from (\n" +
                "\tselect \n" +
                "     donorName,donorId as aa,curColDate,\n" +
                "\t\t (select c.beginDate from t_bims_plasma_collection as c where c.donorId = aa and c.beginDate < curColDate and c.weight > 0 and c.collectionNumberFlag = 'Y' order by c.beginDate desc limit 0,1 ) as collectionDate\n" +
                "\tfrom (\n" +
                "\t   select donor.donorName as donorName,donor.donorId as donorId, currentCollect.beginDate as curColDate from t_bims_plasma_collection as currentCollect left join t_bims_donor as donor on currentCollect.donorId = donor.donorId where currentCollect.collectionNumberFlag = 'Y' \n" +
                "     and currentCollect.beginDate between ? and ? and donor.remark like ? \n" +
                "\t) as a\n" +
                ") as b) as c where c.days > (90) and c.days <= 180", new Object[] {dateStringRangeStart,dateStringRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long jianXianCount = (Long)jianXianList.get(0).get("人数");
        staff.setJianXianCount(jianXianCount.intValue());

        return null;
    }
}
